import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

late Database db;

class DatabaseHelper {
  static final _dbName = 'chat.db';
  static final _dbVersion = 10;

  // 表名
  static const userTable = 'users';
  static const messageTable = 'message';

  DatabaseHelper._();

  static final DatabaseHelper instance = DatabaseHelper._();

  static Database? _database;

  Future<Database> get database async => _database ?? await _initDatabase();

  _initDatabase() async {
    var documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _dbName);
    return await openDatabase(path,
        version: _dbVersion, onCreate: _onCreate, onUpgrade: _onUpgrade);
  }

  Future _onCreate(Database db, int version) async {
    // 创建users表
    await db.execute('''
          CREATE TABLE $userTable (
            id INTEGER PRIMARY KEY,
            uid TEXT NOT NULL,
            firstName TEXT,
            lastName TEXT,
            nikeName TEXT NOT NULL,
            avatarUrl TEXT NOT NULL
          )
          ''');

    // 创建products表
    await db.execute('''
          CREATE TABLE $messageTable (
            id INTEGER PRIMARY KEY,
            msgId TEXT NOT NULL,
            cid  INTEGER ,
            senderId INTEGER NOT NULL,
            receiverId INTEGER NOT NULL,
            name TEXT ,
            createdAt INTEGER NOT NULL,
            status TEXT NOT NULL,
            type TEXT NOT NULL,
            content TEXT NOT NULL
          )
          ''');
  }

  // 更新数据库结构时使用的方法
  Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // if (oldVersion < newVersion) {
    // 执行数据库升级相关的操作
    if (oldVersion == 1) {
      // 从版本1升级到版本2: 添加新表orders
      await db.execute('''
         DROP TABLE  $messageTable
       ''');

      // 创建products表
      await db.execute('''
          CREATE TABLE $messageTable (
            id INTEGER PRIMARY KEY,
            msgId TEXT NOT NULL,
            cid  INTEGER ,
            senderId INTEGER NOT NULL,
            receiverId INTEGER NOT NULL,
            name TEXT ,
            createdAt INTEGER NOT NULL,
            status TEXT NOT NULL,
            type TEXT NOT NULL,
            content TEXT NOT NULL
          )
          ''');
    }
  }

  // 增删改查示例（以users表为例）
  Future<int> insertUser(Map<String, dynamic> row) async {
    Database db = await instance.database;
    return await db.insert(userTable, row);
  }

  Future<bool> cleanCache() async {
    Database db = await instance.database;
    db.delete(messageTable);
    db.delete(userTable);
    return true;
  }

  ///  List<Map> maps = await db.query(tableTodo,
  ///      columns: ['columnId', 'columnDone', 'columnTitle'],
  ///      where: 'columnId = ?',
  ///      whereArgs: [id]);
  ///
  Future<List<Map<String, dynamic>>> queryAllUsers() async {
    Database db = await instance.database;
    return await db.query(userTable);
  }

  Future<int> updateUser(Map<String, dynamic> row) async {
    Database db = await instance.database;
    int uid = row['uid'];
    return await db.update(userTable, row, where: 'uid = ?', whereArgs: [uid]);
  }

  Future<int> deleteUser(int uid) async {
    Database db = await instance.database;
    return await db.delete(userTable, where: 'uid = ?', whereArgs: [uid]);
  }

  // 增删改查示例（以users表为例）
  Future<int> insertMessage(Map<String, dynamic> row) async {
    try{
      Database db = await instance.database;
      return await db.insert(messageTable, row);
    }catch(e){

    }
    return -1;
  }

  // 增删改查示例（以users表为例）
  Future<int> deleteMessage(String msgId) async {
    Database db = await instance.database;
    return await db.delete(messageTable, where: 'msgId = ?', whereArgs: [msgId]);
  }

  Future<List<Map<String, dynamic>>> queryMessagesInConv(num senderId,num receiverId) async {
    Database db = await instance.database;
    return await db.query(messageTable,where: 'senderId= ? AND receiverId= ? ',whereArgs: [senderId,receiverId]);
  }

// 增删改查示例（以users表为例）
}
